package com.elitel.frame.base.controller;

import com.elitel.frame.base.component.exportprojectdata.DefaultDBConnInfo;
import com.elitel.frame.base.service.CfgDbService;
import com.elitel.frame.base.service.CfgTableService;
import com.elitel.frame.business.service.DataBaseMetadataService;
import com.elitel.frame.main.common.datasource.AbstractDynamicDataSource;
import com.elitel.frame.main.entity.CfgDb;
import com.elitel.frame.main.entity.CfgTable;
import com.elitel.frame.main.entity.DataBaseType;
import com.elitel.frame.main.entity.vo.PageRequestVo;
import com.elitel.frame.main.entity.vo.PageResponseVo;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.*;

/**
 * @author guoyanfei @Description 数据库链接配置服务
 */
@Controller
@RequestMapping("/system/dataSource")
public class DataSourceController  {
    private static final Logger logger = LoggerFactory.getLogger(DataSourceController.class);
    @Autowired
    private CfgDbService cfgDbService;
    @Autowired
    private CfgTableService cfgTableService;
    @Autowired
    private DataBaseMetadataService dataBaseMetadataService;
    //  @Autowired private RedisConnInfo redisConnInfo;
    @Autowired
    private DefaultDBConnInfo defaultDBConnInfo;
    @Autowired
    private AbstractDynamicDataSource abstractDynamicDataSource;

    @Autowired
    private Environment environment;

    private static final String DisplayPassword = "**********";

    /**
     * 默认JDBC URL
     */
    private static final String OPT_TYPE_URL = "URL";

    /**
     * @return PageResponseVo<CfgDb>(分页显示bootstrap Table数据)
     * @throws @Title: getDataSourceByPage @Description: TODO(分页查询数据库链接配置表信息)
     * @author guoyanfei
     */
    @RequestMapping("/list")
    @ResponseBody
    public PageResponseVo<CfgDb> getDataSourceByPage(
            HttpServletRequest request, HttpServletResponse response) {
        PageResponseVo<CfgDb> pagelist = new PageResponseVo<CfgDb>();
        try {
            PageRequestVo<CfgDb> pr = new PageRequestVo<CfgDb>();
            int pageNo = Integer.valueOf(request.getParameter("pageNo")).intValue();
            int pageSize = Integer.valueOf(request.getParameter("pageSize")).intValue();
            String seachTxt = request.getParameter("seachTxt");
            pr.setSearchContent(seachTxt);
            pr.setPageNo(pageNo);
            pr.setPageSize(pageSize);
            pagelist = cfgDbService.getListByPage(pr);
            for (CfgDb cfgDb : pagelist.getRows()) {
                // don't show password in api
                cfgDb.setDbPwd(DisplayPassword);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return pagelist;
    }

    /**
     * @return
     * @throws @Title: deleteDataSource @Description: TODO(批量删除数据库链接配置表信息)
     * @author guoyanfei
     */
    @RequestMapping("/delData")
    @ResponseBody
    public Integer deleteDataSource(HttpServletRequest request, HttpServletResponse response) {
        Integer result = 0;
        try {
            String keys = request.getParameter("keys");
            String[] keyarray = keys.split(",");
            List<String> keyList = Arrays.asList(keyarray);

            String isCheck = request.getParameter("isCheck");
            if (isCheck.equals("true")) {
                // 验证库是否存在表配置信息
                for (String strkey : keyList) {
                    List<CfgTable> cfgTables = cfgTableService.getListbyConn(strkey);
                    if (cfgTables != null && cfgTables.size() > 0) {
                        result = -1;
                        break;
                    }
                }
            }
            if (result != -1) {
                cfgDbService.deleteBatchByKey(keyList);
                //                deleteDataSources(keyList);
                deleteDBSourceData(keyList);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
        return result;
    }

    private void  deleteDBSourceData(List<String> keyList) {
//    Jedis jedis = new Jedis(redisConnInfo.redisIP, redisConnInfo.redisPort);
        //        jedis.auth(redisConnInfo.password);
        for (String key : keyList) {
            // 删除redis数据
//      Set<String> set = jedis.keys("CFG_FIELD_" + key + "_*");
//      String deltabkey = "CFG_TABLE_" + key;
//      set.add(deltabkey);
//      for (String delkey : set) {
//        if (jedis.exists(delkey)) {
//          jedis.del(delkey);
//        }
//      }

            // 删除mysql数据
            try {
                Class.forName(defaultDBConnInfo.driverName);
                String url = defaultDBConnInfo.url;
                String user = defaultDBConnInfo.userName;
                String pwd = defaultDBConnInfo.password;
                try {
                    Connection conn = java.sql.DriverManager.getConnection(url, user, pwd);
                    Statement statement = conn.createStatement();
                    String sql1 = "delete from cfg_table where DT_CONN = '" + key + "'";
                    String sql2 = "delete from cfg_field where DT_CONFIG_ID like '" + key + "_%'";
                    statement.execute(sql1);
                    statement.execute(sql2);
                    statement.close();
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            } catch (ClassNotFoundException e) {
                e.printStackTrace();
            }
        }
//    jedis.close();
    }

    /**
     * @param cfgDb(数据源实体)
     * @return 插入条数
     * @throws @Title: addDataSource @Description: TODO(新增数据库链接配置表信息)
     * @author guoyanfei
     */
    @RequestMapping("/add")
    @ResponseBody
    public int addDataSource(
            @RequestBody CfgDb cfgDb, HttpServletRequest request, HttpServletResponse response) {
        int result = 0;
        try {
            String property = environment.getProperty("kudu.enabled");
            if (DataBaseType.KUDU.equals(cfgDb.getDbVersion()) && !"true".equals(property)) {
                return 0;
            }
            cfgDb.setDbUrl(setConfigDb(cfgDb));
            checkSchema(cfgDb);
            result = cfgDbService.insertData(cfgDb);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }

        return result;
    }

    /**
     * 当为oracle或达梦数据库时，若模式为空，则将模式名指定为用户同名的默认模式名
     *
     * @param cfgDb
     */
    private void checkSchema(CfgDb cfgDb) {
        if ("ORACLE".equals(cfgDb.getDbVersion()) || "DM8".equals(cfgDb.getDbVersion())) {
            if (StringUtils.isBlank(cfgDb.getDbSchema())) {
                cfgDb.setDbSchema(cfgDb.getDbUser());
            }
        }
    }

    /**
     * @return CfgDb实体
     * @throws @Title: selectCfgDbBydbKey @Description: TODO(通过主键dbKey查询数据库链接配置表信息)
     * @author guoyanfei
     */
    @RequestMapping("/selectBykey")
    @ResponseBody
    public CfgDb selectCfgDbBydbKey(HttpServletRequest request, HttpServletResponse response) {
        String key = request.getParameter("key");
        CfgDb cfgDb = cfgDbService.selectByKey(key);
        // don't expose password
        if (cfgDb != null && cfgDb.getDbPwd() != null) {
            cfgDb.setDbPwd(DisplayPassword);
        }
        return cfgDb;
    }

    /**
     * @return 修改条数
     * @throws @Title: updateByKey @Description: TODO(修改数据库链接配置表信息)
     * @author guoyanfei
     */
    @RequestMapping("/update")
    @ResponseBody
    public int updateByKey(
            @RequestBody CfgDb cfgDb, HttpServletRequest request, HttpServletResponse response) {
        int result = 0;
        try {
            // here check
            if (DisplayPassword.equals(cfgDb.getDbPwd())) {
                // find before password
                CfgDb cfgDbBefore = cfgDbService.selectByKey(cfgDb.getDbKey());
                cfgDb.setDbPwd(cfgDbBefore.getDbPwd());
            }
            cfgDb.setDbUrl(setConfigDb(cfgDb));
            checkSchema(cfgDb);
            result = cfgDbService.updateData(cfgDb);
            if (result > 0) {
                List<String> keys = new ArrayList<>();
                keys.add(cfgDb.getDbKey());
                deleteDataSources(keys);
            }
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }

        return result;
    }
//todo  线程安全问题

    /**
     * 修改配置的数据源后，删除已在连接池中存在的旧的数据源 create by guoyanfei on 2019/07/23
     */
    private void deleteDataSources(List<String> keys) {
        Map<Object, Object> dataSources = abstractDynamicDataSource.targetDataSources;
        if (keys.size() > 0 && dataSources.size() > 0) {
            for (Map.Entry<Object, Object> entry : dataSources.entrySet()) {
                String mapkey = entry.getKey().toString();
                for (String key : keys) {
                    if (mapkey.equals(key)) {
                        dataSources.remove(mapkey);
                    }
                }
            }
        }
    }

    /**
     * @return true(成功)、false(失败)
     * @throws @Title: testDataSource @Description: TODO(测试数据库链接是否正确)
     * @author guoyanfei
     */
    @RequestMapping("/testDataSource")
    @ResponseBody
    public boolean testDataSource(@RequestBody CfgDb cfgDb) {
        try {
            if (DisplayPassword.equals(cfgDb.getDbPwd())) {
                // find before password
                CfgDb cfgDbBefore = cfgDbService.selectByKey(cfgDb.getDbKey());
                cfgDb.setDbPwd(cfgDbBefore.getDbPwd());
            }

            cfgDb.setDbUrl(setConfigDb(cfgDb));
            cfgDb.setDbSign(UUID.randomUUID().toString());
            return dataBaseMetadataService.testDataSource(cfgDb);
        } catch (Exception e) {
            logger.error(e.getMessage(), e);
            return false;
        }
    }

    /**
     * @return 数据库连接字符串
     * @throws @Title: setConfigDb @Description: TODO(拼接各个版本数据库连接字符串)
     * @author guoyanfei
     */
    public String setConfigDb(CfgDb cfgDb) {
        String result = null;
        switch (cfgDb.getDbVersion()) {
            case "ORACLE":
                if (OPT_TYPE_URL.equals(cfgDb.getOptType())) {
                    result = cfgDb.getDbUrl();
                } else {
                    String url = cfgDb.getDbIp() + ":" + cfgDb.getDbPort() + ("SID".equals(cfgDb.getExt1()) ? ":" : "/") + cfgDb.getDbName();
                    result = String.format("jdbc:oracle:thin:@%s", url);
                }
                break;
            case "MYSQL":
                result =
                        String.format(
                                "jdbc:mysql://%s/%s?useUnicode=true&characterEncoding=UTF-8&zeroDateTimeBehavior=convertToNull",
                                cfgDb.getDbIp(), cfgDb.getDbName());
                break;
            case "SQLSERVER":
                result = String.format("jdbc:sqlserver://%s;port=%s;databaseName=%s;trustServerCertificate=true", cfgDb.getDbIp(), cfgDb.getDbPort(), cfgDb.getDbName());
                break;
            case "KingbaseV8":
                result = String.format("jdbc:kingbase8://%s/%s", cfgDb.getDbIp(), cfgDb.getDbName());
                break;
            case "DM8":
                if (OPT_TYPE_URL.equals(cfgDb.getOptType())) {
                    String dbUrl = cfgDb.getDbUrl();
                    if (dbUrl != null) {
                        if (!dbUrl.toLowerCase().contains("schema")) {
                            result = String.format(cfgDb.getDbUrl() + "?schema=%s", cfgDb.getDbSchema());
                        } else {
                            result = dbUrl.substring(0, dbUrl.lastIndexOf("schema") + 6) + "=" + cfgDb.getDbSchema();
                        }
                    }
                } else {
                    result = String.format("jdbc:dm://%s:%s?schema=%s", cfgDb.getDbIp(), cfgDb.getDbPort(), cfgDb.getDbSchema());
                }
                break;
            case "POSTGRESQL":
                if (OPT_TYPE_URL.equals(cfgDb.getOptType())) {
                    String dbUrl = cfgDb.getDbUrl();
                    if (dbUrl != null) {
                        if (!dbUrl.toLowerCase().contains("currentSchema")) {
                            result = String.format(cfgDb.getDbUrl() + "?currentSchema=%s", cfgDb.getDbSchema());
                        } else {
                            result = dbUrl.substring(0, dbUrl.lastIndexOf("currentSchema") + 13) + "=" + cfgDb.getDbSchema();
                        }
                    }
                } else {
                    result = String.format("jdbc:postgresql://%s:%s/%s?currentSchema=%s", cfgDb.getDbIp(), cfgDb.getDbPort(), cfgDb.getDbName(), cfgDb.getDbSchema());
                }
                break;
            case "MongoDB":
                if (OPT_TYPE_URL.equals(cfgDb.getOptType())) {
                    return cfgDb.getDbUrl();
                } else {
                    if (StringUtils.isNotBlank(cfgDb.getDbUser()) && StringUtils.isNotBlank(cfgDb.getDbPwd())) {
                        result = String.format( "mongodb://%s:%s@%s/%s", cfgDb.getDbUser(), cfgDb.getDbPwd(), cfgDb.getDbIp(), cfgDb.getDbName());
                    } else {
                        result = String.format("mongodb://%s/%s", cfgDb.getDbIp(), cfgDb.getDbName());
                    }
                }   
                break;
            case "KUDU":
                result = String.format("jdbc:impala://%s:21050/%s", cfgDb.getDbIp(), cfgDb.getDbName());
                break;
            default:
                break;
        }
        return result;
    }
}
